<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);

							// Create the prepared statement and use it to
							// INSERT the student username INTO the account table.
							PreparedStatement pstmt = conn
									.prepareStatement("INSERT INTO account VALUES (?, ?, ?)");
							pstmt.setString(1, request.getParameter("username"));
							pstmt.setString(2, "ChangeMe");
							pstmt.setString(3, request.getParameter("role"));
							pstmt.executeUpdate();
							
							// INSERT the student attributes INTO the student table.
							pstmt = conn.prepareStatement("INSERT INTO student VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
							pstmt.setString(1, request.getParameter("student_id"));
							pstmt.setString(2, request.getParameter("residency"));
							pstmt.setString(3, request.getParameter("first_name"));
							pstmt.setString(4, request.getParameter("middle_name"));
							pstmt.setString(5, request.getParameter("last_name"));
							pstmt.setInt(6, Integer.parseInt(request.getParameter("ssn")));
							pstmt.setString(7, request.getParameter("enrollment"));
							pstmt.setString(8, request.getParameter("username"));
							pstmt.executeUpdate();
							
							// INSERT the additional attributes INTO the grad_student table.
							String table = request.getParameter("level").equals("MS") ?
									"ms_student" : "phd_student";
							String specialColumn = request.getParameter("level").equals("MS") ?
									"in_5_year_class" : "has_candidacy";
							pstmt = conn.prepareStatement("INSERT INTO " + table + " VALUES (?, ?, ?)");
							
							pstmt.setString(1, request.getParameter("student_id"));
							pstmt.setString(2, request.getParameter(specialColumn));
							pstmt.setString(3, request.getParameter("department"));
							pstmt.executeUpdate();
							
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the student attributes in the student table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE student SET residency = ?, first_name = ?, " +
	                            "middle_name = ?, last_name = ?, ssn = ?, enrollment_status = ? " +
	                            "WHERE student_id = ?");

	                        pstmt.setString(1, request.getParameter("residency"));
	                        pstmt.setString(2, request.getParameter("first_name"));
	                        pstmt.setString(3, request.getParameter("middle_name"));
	                        pstmt.setString(4, request.getParameter("last_name"));
	                        pstmt.setInt(5, Integer.parseInt(request.getParameter("ssn")));
	                        pstmt.setString(6, request.getParameter("enrollment"));
	                        pstmt.setString(7, request.getParameter("student_id"));
	                        pstmt.executeUpdate();
	                        
	                        // Update the ms_student or phd_student table.
	                        String level = request.getParameter("level");
	                        if (level.equals("MS")) {
	                      		pstmt = conn.prepareStatement(
		                    		"UPDATE ms_student SET in_5_year_class = ?, department = ? " +
		                    		"WHERE student_id = ?");
	                        	pstmt.setString(1, request.getParameter("in_5_year_class"));
	                        	pstmt.setString(2, request.getParameter("department"));
	                        	pstmt.setString(3, request.getParameter("student_id"));
	                        	pstmt.executeUpdate();
	                        }
	                        else if (level.equals("PhD")) {
	                        	pstmt = conn.prepareStatement(
			                    	"UPDATE phd_student SET has_candidacy = ?, department = ? " +
			                    	"WHERE student_id = ?");
		                        pstmt.setString(1, request.getParameter("has_candidacy"));
		                        pstmt.setString(2, request.getParameter("department"));
		                        pstmt.setString(3, request.getParameter("student_id"));
		                        pstmt.executeUpdate();
	                        }

	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("delete")) {
							conn.setAutoCommit(false);
							String student_id = request.getParameter("student_id");
							
							//delete attendance
							PreparedStatement pstmt = conn.prepareStatement("DELETE FROM attendance WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete past degree 
							pstmt = conn.prepareStatement("DELETE FROM degree WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete probation
							pstmt = conn.prepareStatement("DELETE FROM probation WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete enrollment
							pstmt = conn.prepareStatement("DELETE FROM enroll WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete advisor
							pstmt = conn.prepareStatement("DELETE FROM advice WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete committee
							pstmt = conn.prepareStatement("DELETE FROM committee WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete ms student
							pstmt = conn.prepareStatement("DELETE FROM ms_student WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete phd student
							pstmt = conn.prepareStatement("DELETE FROM phd_student WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							//delete student
							pstmt = conn.prepareStatement("DELETE FROM student WHERE student_id=?");
							pstmt.setString(1, student_id);
							pstmt.executeUpdate();
							
							
							conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						// Create the statement
						Statement statement = conn.createStatement();
						
						// SELECT the departments FROM the department table.
						ResultSet rsDept = statement.executeQuery("SELECT * FROM department");
						// Save departments for later use.
						List<String> departments = new ArrayList<String>();
						while (rsDept.next()) {
							departments.add(rsDept.getString("department"));
						}
				%>
				<table border="1">
					<tr>
						<th>Level</th>
						<th>Department</th>
						<th>Student ID</th>
						<th>Username</th>
						<th>First name</th>
						<th>Middle name</th>
						<th>Last name</th>
						<th>Residency</th>
						<th>SSN</th>
						<th>Enrollment</th>
						<th>In 5-year class (MS only)</th>
						<th>Has candidacy (PhD only)</th>
					</tr>
					<tr>
						<form action="grad_student.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<input type="hidden" value="student" name="role">
							<th>
								<select name="level">
									<option value="MS">MS</option>
									<option value="PhD">PhD</option>
								</select>
							</th>
							<th>
								<select name="department" style="width:100px">
								<%
									for (String department : departments) {
								%>
									<option value="<%= department %>"><%= department %></option>
								<% 
									}
								%>
								</select>
							</th>
							<th><input value="" name="student_id" size="10"></th>
							<th><input value="" name="username" size="15"></th>
							<th><input value="" name="first_name" size="15"></th>
							<th><input value="" name="middle_name" size="15"></th>
							<th><input value="" name="last_name" size="15"></th>
							<th>
								<select name="residency" style="width:100px">
									<option value="California">California resident</option>
									<option value="non-CA">Non-CA US student</option>
									<option value="foreign">foreign student</option>
								</select>
							</th>
							<th><input value="" name="ssn" size="10"></th>
							<th>
								<select name="enrollment" style="width:100px">
									<option value="YES">Enrolled</option>
									<option value="NO">Not enrolled</option>
								</select>
							</th>
							<th>
								<select name="in_5_year_class" style="width:100px">
									<option value=""></option>
									<option value="YES">YES</option>
									<option value="NO">NO</option>
								</select>
							</th>
							<th>
								<select name="has_candidacy" style="width:100px">
									<option value=""></option>
									<option value="YES">YES</option>
									<option value="NO">NO</option>
								</select>
							</th>
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
							// Use the created statement to SELECT
							// the student attributes FROM the student table.
							String subQuery =
								"SELECT student_id, has_candidacy, '' AS in_5_year_class, department, " + 
								"'PhD' AS level FROM phd_student UNION SELECT student_id, '' AS has_candidacy, " +
								"in_5_year_class, department, 'MS' AS level FROM ms_student";
							ResultSet rs = statement.executeQuery("SELECT * FROM student s, (" + subQuery + ") u " + 
									"WHERE s.student_id = u.student_id");
							
							// Iterate over the ResultSet
							while (rs.next()) {
					%>
					<tr>
                        <form action="grad_student.jsp" method="POST">
                            <input type="hidden" value="update" name="action">
                            <td>
                            	<input type="hidden" value="<%= rs.getString("level") %>" name="level">
                            	<%= rs.getString("level") %>
                            </td>
                            <td>
                            	<% String dept = rs.getString("department"); %>
                            	<select name="department" style="width:100px">
								<%
									for (String department : departments) {
								%>
									<option value="<%= department %>" 
											<% if (dept.equals(department)) out.print("selected='selected'"); %>>
										<%= department %>
									</option>
								<% 
									}
								%>
								</select>
                            </td>
                            <td>
                            	<input type="hidden" value="<%= rs.getString("student_id") %>" name="student_id">
                                <%= rs.getString("student_id") %>
                            </td>
                            <td>
                                <input type="hidden" value="<%= rs.getString("username") %>" name="username">
                                <%= rs.getString("username") %>
                            </td>
                            <td>
                                <input value="<%= rs.getString("first_name") %>" name="first_name" size="15">
                            </td>
                            <td>
                                <input value="<%= rs.getString("middle_name") %>" name="middle_name" size="15">
                            </td>
                            <td>
                                <input value="<%= rs.getString("last_name") %>" name="last_name" size="15">
                            </td>
                            <td>
                                <% String residency = rs.getString("residency"); %>
                                <select name="residency" style="width:100px">
									<option value="California" <% if (residency.equals("California")) out.print("selected='selected'"); %>>California resident</option>
									<option value="non-CA" <% if (residency.equals("non-CA")) out.print("selected='selected'"); %>>Non-CA US student</option>
									<option value="foreign" <% if (residency.equals("foreign")) out.print("selected='selected'"); %>>foreign student</option>
								</select>
                            </td>
                            <td>
                                <input value="<%= rs.getInt("ssn") %>" name="ssn" size="10">
                            </td>
                            <td>
                                <% String enrollment = rs.getString("enrollment_status"); %>
                            	<select name="enrollment" style="width:100px">
									<option value="YES" <% if (enrollment.equals("YES")) out.print("selected='selected'"); %>>Enrolled</option>
									<option value="NO" <% if (enrollment.equals("NO")) out.print("selected='selected'"); %>>Not enrolled</option>
								</select>
                            </td>
                            <td>
                                <% String fiveYear = rs.getString("in_5_year_class"); %>
                                <select name="in_5_year_class" style="width:100px">
                                	<option value="" <% if (fiveYear.isEmpty()) out.print("selected='selected'"); %>></option>
									<option value="YES" <% if (fiveYear.equals("YES")) out.print("selected='selected'"); %>>YES</option>
									<option value="NO" <% if (fiveYear.equals("NO")) out.print("selected='selected'"); %>>NO</option>
								</select>
                            </td>
                            <td>
                                <% String candidacy = rs.getString("has_candidacy"); %>
                                <select name="has_candidacy" style="width:100px">
                               		<option value="" <% if (candidacy.isEmpty()) out.print("selected='selected'"); %>></option>
									<option value="YES" <% if (candidacy.equals("YES")) out.print("selected='selected'"); %>>YES</option>
									<option value="NO" <% if (candidacy.equals("NO")) out.print("selected='selected'"); %>>NO</option>
								</select>
                            </td>
                        	<td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        <form action="grad_student.jsp" method="POST">
                            <input type="hidden" value="delete" name="action">
                            <input type="hidden" 
                                value="<%= rs.getString("student_id") %>" name="student_id">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
					<%
							}
					%>
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>